package com.scalar.forecast.forecastSetup.dao;

import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.forecast.forecastSetup.ForecastEntryField;
import com.scalar.forecast.forecastSetup.ForecastTimeFrame;
import com.scalar.forecast.forecastSetup.LevelBean;
import com.scalar.forecast.utils.ForecastSqlQueryMappings;
import com.scalar.ScalarException;


import java.util.ArrayList;
import java.sql.*;

/**
 * User: Sujan Kumar
 * Date: Feb 8, 2005
 * Time: 3:56:39 PM
 */
public class ForecastSetupDAO
{
     public static ArrayList getForecastSetupFields( DBTransaction dbTransaction ) throws ScalarException
     {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rset = null;
         ForecastEntryField forecastSetupField = null;
         ArrayList forecastSetupFields = new ArrayList();
         boolean success = false;
         try
         {
             if ( dbTransaction == null )
             {
                conn   = DataSource.getConnection();
             }
             else
             {
                 conn = dbTransaction.getConnection();
             }
             pstmt  = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.setup.getForecastSetupFields") );
             rset   = pstmt.executeQuery();
             while ( rset.next () )
             {
                 forecastSetupField = new ForecastEntryField();
                 forecastSetupField.setRowId( rset.getInt ( "RowId") );
                 forecastSetupField.setEntryName( rset.getString( "EntryName" ) );
                 forecastSetupField.setEntryOrder( rset.getInt ( "EntryOrder" ) );
                 forecastSetupField.setEntrySelected( rset.getInt ( "EntrySelect") == 0 ? true : false );
                 forecastSetupFields.add ( forecastSetupField );
             }
             success =true;
         }
         catch ( Exception e )
         {
             System.out.println ( "------Exception While getting the ForecastSetupFields: " );
             e.printStackTrace();
             throw new ScalarException ( "Exception while getting the forecastSetupFields" );
         }
         finally
         {
             if ( dbTransaction == null )
             {
                 if ( conn != null )
                 {
                     try
                     {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                     }
                     catch ( SQLException ignore ){ }
                 }
             }
             if ( rset != null )
             {
                 try { rset.close(); } catch ( SQLException ignore ){ }
             }
             if ( pstmt != null )
             {
                 try { pstmt.close(); } catch ( SQLException ignore ){ }
             }
             if ( conn != null )
             {
                 try { conn.close(); } catch ( SQLException ignore ){ System.out.println ( "in getForecastSetupFields:" + ignore.getMessage() ); }
             }
         }
         return forecastSetupFields;
    }
    public static ForecastTimeFrame getForecastTimeFrame( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ForecastTimeFrame forecastTimeFrame = new ForecastTimeFrame();
        boolean success = false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get ( "forecast.setup.getForecastTimeFrame") );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                forecastTimeFrame.setRowId( rset.getInt ( "RowId") );
                forecastTimeFrame.setTfName( rset.getString ( "TFName" ) );
                forecastTimeFrame.setTfCompany( rset.getString ( "TFCompany" )  );
                forecastTimeFrame.setTfSelect( rset.getInt ( "TFSelect" ) == 0 ? true : false );
                forecastTimeFrame.setTfMeasureType( rset.getString ( "TFMeasureType" ) );
                forecastTimeFrame.setTfCycle( rset.getString( "TFCycle" ) );
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "---Exception While Fetching The TimeFrameName---" );
            e.printStackTrace();
            throw new ScalarException( "Could Not get Forecast Time Frame" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                     }
                     catch ( SQLException ignore ){ }
                 }
             }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { System.out.println ( "in getForecastTimeFrame:" + ignore.getMessage() ); }
            }
        }
        return forecastTimeFrame;
    }
    public static ArrayList getForecastLevels( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList levels = new ArrayList();
        LevelBean levelBean = null;
        boolean success = false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.setup.getForecastLevels" ) );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                levelBean = new LevelBean();
                levelBean.setLevelId( rset.getInt ( "LevelId" ) );
                levelBean.setLevelName( rset.getString ( "LevelName" ) );
                levelBean.setLevelOrder( rset.getString( "LevelOrder") );
                levelBean.setSelected( rset.getInt ( "LevelSelect") == 0 ? true : false);
                levels.add ( levelBean );
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "----Exception while fetching levels ----" );
            e.printStackTrace();
            throw new ScalarException( "Could Not Get Forecast Levels");
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                     }
                     catch ( SQLException ignore ){ }
                 }
             }
             if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {System.out.println ( "in getForecastLevels:" + ignore.getMessage() ); }
            }
        }
        return levels;
    }
    public static ArrayList getForecastEntryFields( DBTransaction dbTransaction )
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ForecastEntryField forecastEntryField = null;
        ArrayList forecastEntryFields = new ArrayList();
        boolean success = false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get ( "forecast.setup.getForecastEntryFields" ) );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                forecastEntryField = new ForecastEntryField();
                forecastEntryField.setRowId( rset.getInt ( "RowID" ) );
                forecastEntryField.setEntryName( rset.getString( "EntryName" ) );
                forecastEntryField.setEntrySelected( rset.getInt ( "EntrySelect" ) == 0 ? true : false );
                forecastEntryFields.add ( forecastEntryField );
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "---Exception while fetching the forecastEntryFields---" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                     }
                     catch ( SQLException ignore ){ }
                 }
             }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return forecastEntryFields;
    }
    public static boolean updateForecastTimeFrame ( ForecastTimeFrame forecastTimeFrame, DBTransaction dbTransaction ) throws ScalarException
    {
        boolean success = false;
        Connection conn = null;
        PreparedStatement pstmt = null;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get ( "forecast.setup.deSelectForecastTimeFrame" ) );
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.setup.updateForecastTimeFrame" ) );
            int tfCompany = 0;
            try
            {
                tfCompany = Integer.parseInt ( forecastTimeFrame.getTfCompany() );
            }
            catch ( NumberFormatException e )
            {
                tfCompany = 0;
            }
            pstmt.setInt ( 1, tfCompany );
            pstmt.setInt ( 2, 0 );
            pstmt.setString ( 3, forecastTimeFrame.getTfMeasureType() );
            pstmt.setString ( 4, forecastTimeFrame.getTfCycle() );
            pstmt.setString ( 5, forecastTimeFrame.getTfName() );

            int k = pstmt.executeUpdate();
            if ( k == 1 )
            {
                success = true;
            }
        }
        catch ( Exception e )
        {
            success = false;
            System.out.println ( "---Exception While Updating ForecastTimeFrame----" );
            e.printStackTrace();
            throw new ScalarException ( "Exception While Updating ForecastTimeFrame:" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( success )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
                catch ( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return success;
    }
    public static boolean saveForecastLevels ( ArrayList levels, DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        LevelBean levelBean = null;
        boolean success = false;
        if ( levels != null )
        {
            try
            {
                if ( dbTransaction == null )
                {
                    conn = DataSource.getConnection();
                }
                else
                {
                    conn = dbTransaction.getConnection();
                }
                int levelCount =levels.size();
                for ( int i=0; i < levelCount; i++ )
                {
                    levelBean = ( LevelBean )levels.get ( i );
                    if ( levelBean.getLevelId() != -1 )
                    {
                        pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.setup.saveForecastLevel" ) );
                        pstmt.setString ( 1, levelBean.getLevelName() );
                        pstmt.setInt ( 2, Integer.parseInt ( levelBean.getLevelOrder() ) );
                        pstmt.setInt ( 3, levelBean.isSelected() == true ? 0 : 1 );
                        pstmt.setInt ( 4, levelBean.getLevelId() );
                        pstmt.executeUpdate();
                        pstmt.close();
                    }
                    else
                    {
                        if ( levelBean.isSelected() )
                        {
                            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.setup.insertForcastLevel" ) );
                            pstmt.setString ( 1, levelBean.getLevelName() );
                            pstmt.setInt ( 2, Integer.parseInt ( levelBean.getLevelOrder() ) );
                            pstmt.setInt ( 3, 0 );
                            pstmt.executeUpdate();
                            pstmt.close();
                        }
                    }
                }
                success = true;
            }
            catch ( Exception e )
            {
                success = false;
                System.out.println ( "--- Exception While saving ForecastLevels----" );
                e.printStackTrace();
                throw new ScalarException ( "Exception While Saving ForecastLevels:" );
            }
            finally
            {
                if ( dbTransaction == null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
                if ( pstmt != null )
                {
                    try { pstmt.close(); } catch( SQLException ignore ){ }
                }
                if ( conn != null )
                {
                    try { conn.close(); } catch ( SQLException ignore ){ }
                }
            }
        }
        return success;
    }
    public static boolean updateForecastEntryFiels ( ArrayList forecastEntryFields, DBTransaction dbTransaction ) throws ScalarException
    {
        boolean success = false;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ForecastEntryField forecastEntryField = null;
        if ( forecastEntryFields != null )
        {
            try
            {
                if ( dbTransaction == null )
                {
                    conn = DataSource.getConnection();
                }
                else
                {
                    conn = dbTransaction.getConnection();
                }
                pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get( "forecast.forecastSetup.update.forecastEntrySetup" ) );
                int listSize = forecastEntryFields.size();
                for ( int i = 0; i < listSize; i++ )
                {
                    forecastEntryField = ( ForecastEntryField )forecastEntryFields.get( i );
                    pstmt.setInt( 1, forecastEntryField.isEntrySelected() == true ? 0 : 1 );
                    pstmt.setInt ( 2, forecastEntryField.getRowId() );
                    pstmt.executeUpdate();
                    pstmt.clearParameters();
                }
                pstmt.close();
                pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get( "forecast.forecastSetup.synchronize.editColDisp" ) );
                pstmt.execute();
                success = true;
            }
            catch ( Exception e )
            {
                success = false;
                System.out.println ( "---Exception While Updating ForecastEntryFields---" );
                e.printStackTrace();
                throw new ScalarException ( "Exception While Updating ForecastEntryFields" );
            }
            finally
            {
                if ( dbTransaction == null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
                if ( pstmt != null )
                {
                    try { pstmt.close(); } catch( SQLException ignore ){ }
                }
                if ( conn != null )
                {
                    try { conn.close(); } catch ( SQLException ignore ){ }
                }
            }
        }
        return success;
    }
    public static void updateForecastEntry ( boolean byProduct, boolean byCustomer, boolean byProject, boolean byODM, DBTransaction dbTransaction ) throws ScalarException
    {
        updateForecastEntrySetup ( "By Product", byProduct, dbTransaction );
        updateForecastEntrySetup ( "By Customer", byCustomer, dbTransaction );
        updateForecastEntrySetup ( "By Project",  byProject, dbTransaction  );
        updateForecastEntrySetup ( "By ODM",  byODM, dbTransaction );
    }
    private static boolean updateForecastEntrySetup ( String entryName, boolean isSelected, DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.setup.updateForecastEntrySetup") );
            pstmt.setInt ( 1, isSelected == true ? 0 : 1 );
            pstmt.setString ( 2, entryName );
            int k = pstmt.executeUpdate();
            if ( k == 0 )
            {
                throw new ScalarException ( "Could Not Save the ForecastEntry For the EntryName:" + entryName );
            }
        }
        catch ( Exception e )
        {
            success = false;
            System.out.println ( "---Exception while UpdatingForecastEntry For the EntryName:" + entryName );
            e.printStackTrace();
            throw new ScalarException( "Could Not Update ForecastEntrySetup" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( success )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
                catch ( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return false;
    }

     /**
	 * Loads all the products for which Forecast is generated.
	 **/

    public static ArrayList loadFoecastedPoducts( DBTransaction dbTransaction, int projectId ) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        ArrayList forecastedProducts = new ArrayList();

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get("forecast.projectSetup.loadSalesForecastProducts") );
            pstmt.setInt( 1, projectId );

            rs = pstmt.executeQuery();
            while ( rs.next() ) {
                forecastedProducts.add( String.valueOf( rs.getInt("PRODID") ) );
            }
            if ( rs!= null ) {
                rs.close();
            }
            if ( pstmt!= null ) {
                pstmt.close();
            }
            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get("forecast.projectSetup.loadBuildForecastProducts") );
            pstmt.setInt( 1, projectId );

            rs = pstmt.executeQuery();
            while ( rs.next() ) {
                forecastedProducts.add( String.valueOf( rs.getInt("PRODID") ) );
            }

            success = true;
        } catch( Exception e ) {
            e.printStackTrace();
            throw new ScalarException("Could not load Selected Products from database");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return forecastedProducts;
    }
    public static boolean isForecastSnapshotExists(DBTransaction dbTransaction, String snapshotName ) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean exists = false;

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( "SELECT COUNT(*) CNT FROM FORECAST_SNAPSHOT_TABLE WHERE SNAPSHOT_NAME = ?" );
            pstmt.setString( 1, snapshotName );

            rs = pstmt.executeQuery();
            if( rs.next() ) {
                int count = rs.getInt("CNT");
                exists = count >0;
            }
        } catch( Exception e ) {
            e.printStackTrace();
            throw new Exception("Could not check whether snapshot exists or not", e);
        }
        finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }

            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }

        return exists;
    }
    
    public static void saveForecastSnapshot(DBTransaction dbTransaction, int fromMonth, int fromYear, String snapshotName ) throws Exception {
        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }
            cstmt = conn.prepareCall( "{CALL SP_SAVE_FORECAST_SNAPSHOT(?, ?, ?)}" );
            cstmt.setInt(1, fromMonth);
            cstmt.setInt(2, fromYear);
            cstmt.setString( 3, snapshotName );

            cstmt.executeUpdate();
            if(dbTransaction == null) {
                conn.commit();
            }
            success = true;

        } catch( Exception e ) {
            e.printStackTrace();
            throw new Exception("Could not save forecast snapshot", e);
        }
        finally {
            if(dbTransaction == null && conn != null) {
                if(!success) {
                    try {
                        conn.rollback();
                    } catch(Exception ignore) {}
                }
            }
            if (cstmt != null) {
                try {
                    cstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }
}

